﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class SubContractorRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<SubContractorModel> GetAllSubContractors()
        {
            List<SubContractorModel> allSubContractors = new List<SubContractorModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".subcontractor";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    SubContractorModel subContractor = new SubContractorModel();

                    subContractor.SubContractorID = int.Parse(dataReader["in_fldSubContID"] + "");
                    subContractor.SubContractorName = dataReader["vc_fldSubContName"] + "";
                    subContractor.SubContractorCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");

                    allSubContractors.Add(subContractor);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allSubContractors;
        }

        public bool Insert(SubContractorModel subContractorModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".subcontractor (vc_fldSubContName, dt_fldCreate) VALUES('" + subContractorModel.SubContractorName
                    + "', '" + subContractorModel.SubContractorCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public IEnumerable<SelectListItem> GetSubContractorsForDropDownList(List<SubContractorModel> modelList)
        {
            List<SelectListItem> SubContList = new List<SelectListItem>();

            foreach (SubContractorModel subContractor in modelList)
            {
                SelectListItem sListItem = new SelectListItem();
                sListItem.Text = subContractor.SubContractorName;
                sListItem.Value = subContractor.SubContractorName;

                SubContList.Add(sListItem);
            }

            return SubContList;
        }
    }
}